package com.itdct.onflow.com.itdct.onflow.system.generator.util

import cn.hutool.core.util.ReflectUtil
import com.itdct.onflow.core.entity.BaseEntity
import com.itdct.onflow.core.entity.BaseUserEntity
import com.itdct.onflow.core.entity.IdEntity
import com.itdct.onflow.core.extend.logError
import com.itdct.onflow.core.extend.logInfo
import com.itdct.onflow.core.util.TimeUtil
import com.itdct.onflow.generator.model.ClassModel
import java.sql.Connection
import java.sql.ResultSet
import java.sql.Statement
import java.time.LocalDateTime
import java.time.ZoneId
import java.util.*
import kotlin.reflect.KProperty1


/**
 * @author Zhouwx
 * @date 2025/9/8 14:02:17
 * @version 1.0
 * @description 将Bean转为SQL语句
 */
object JdbcHelper {
    fun insert(connection: Connection, classModel: ClassModel, insertEntity: IdEntity, onSql: ((sql: String) -> String)? = null): Long {
        var sql = insertSql(classModel, insertEntity)
        sql = onSql?.invoke(sql) ?: sql
        logInfo("insert sql is $sql")
        var generateId: Long? = null
        val prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        val affectRows = prepareStatement.executeUpdate()
        if (affectRows > 0) {
            generateId = prepareStatement.generatedKeys.use {
                if (it.next()) {
                    it.getLong(1)
                } else {
                    null
                }
            }
            prepareStatement.close()
            return generateId ?: -1L
        } else {
            prepareStatement.close()
            return -1L
        }
    }

    fun update(connection: Connection, classModel: ClassModel, updateEntity: IdEntity, onSql: ((sql: String) -> String)? = null): Int {
        var sql = updateSql(classModel, updateEntity)
        sql = onSql?.invoke(sql) ?: sql
        logInfo("update sql is $sql")
        val prepareStatement = connection.prepareStatement(sql)
        val affectRows = prepareStatement.executeUpdate()
        prepareStatement.close()
        return affectRows
    }

    fun <T : IdEntity> selectById(connection: Connection, classModel: ClassModel, id: Long): T {
        val sql = """
select * from ${classModel.tableName} where id = ${id}
        """.trimIndent()
        val prepareStatement = connection.prepareStatement(sql)
        val resultSet = prepareStatement.executeQuery()
        val clazz = classModel.clazz ?: throw IllegalArgumentException("classModel.clazz is null")
        val entity = resultSetToEntity(clazz, resultSet, classModel)
        return entity as T
    }

    fun <T : IdEntity> selectAll(connection: Connection, classModel: ClassModel): ArrayList<T> {
        val clazz = classModel.clazz ?: throw IllegalArgumentException("classModel.clazz is null")
        var whereSql = ""
        if (BaseEntity::class.java.isAssignableFrom(clazz)) {
            whereSql = "where del = 0"
        }

        val sql = """
select * from ${classModel.tableName} ${whereSql}
        """.trimIndent()
        logInfo("selectByList sql is $sql")
        val prepareStatement = connection.prepareStatement(sql)
        val resultSet = prepareStatement.executeQuery()
        val entityList = ArrayList<T>()
        while (resultSet.next()) {
            val entity = resultSetToEntity(clazz, resultSet, classModel)
            entityList.add(entity as T)
        }
        return entityList
    }

    private fun resultSetToEntity(clazz: Class<out IdEntity>, resultSet: ResultSet, classModel: ClassModel): Any {
        val entity = clazz.constructors.firstOrNull {
            it.parameters.isEmpty()
        }?.newInstance() ?: throw IllegalArgumentException("classModel.clazz.constructors.firstOrNull { it.parameters.isEmpty() } is null")
        val entityClass = entity::class.java
        val fields = ReflectUtil.getFields(entityClass)
        classModel.fieldList.forEach {
            val fieldName = it.name
            val findField = fields.filter {
                it.name == fieldName
            }.firstOrNull()
            if (findField != null) {
                findField.isAccessible = true
                try {
                    val fieldValue = resultSet.getObject(it.columnName)
                    if (fieldValue is Int && it.kClass == Boolean::class) {
                        if (fieldValue == 0) {
                            findField.set(entity, false)
                        } else {
                            findField.set(entity, true)
                        }
                    } else if (fieldValue is LocalDateTime) {
                        val localDateTime = LocalDateTime.now() // 当前本地日期时间
                        // 转换核心步骤: LocalDateTime -> ZonedDateTime -> Instant -> Date
                        val date = Date.from( // 3. 将 Instant 转换为 Date
                            localDateTime.atZone( // 1. 将 LocalDateTime 与指定时区结合，得到 ZonedDateTime
                                ZoneId.systemDefault() // 指定时区（这里用系统默认）
                            ).toInstant() // 2. 将 ZonedDateTime 转换为表示时间点的 Instant
                        )
                        findField.set(entity, date)
                    } else {
                        findField.set(entity, fieldValue)
                    }
                } catch (e: Exception) {
                    logError(e)
                }
            }
        }
        return entity
    }

    /**
     * 查询列表
     * @param connection 数据库连接
     * @param classModel 类模型
     * @param ktPropertiesPairs 查询条件
     * 第一个参数：类属性
     * 第二个参数：操作符
     * 第三个参数：值
     */
    fun <T : IdEntity> selectList(connection: Connection, classModel: ClassModel, vararg ktPropertiesPairs: Triple<KProperty1<T, *>, String, Any?>): ArrayList<T> {
        val whereSql = ktPropertiesPairs.map {
            val fieldName = it.first.name
            val operator = it.second
            val fieldValue = it.third
            val fieldModel = classModel.fieldList.filter {
                it.name == fieldName
            }.firstOrNull()
            if (fieldModel == null) {
                null
            } else {
                val valueToString = valueToString(fieldValue)
                "`${fieldModel.columnName}` ${operator} ${valueToString}"
            }
        }.filter {
            it != null
        }.joinToString(" and ")
        val sql = """
select * from ${classModel.tableName} where ${whereSql}
        """.trimIndent()
        logInfo("selectByList sql is $sql")
        val prepareStatement = connection.prepareStatement(sql)
        val resultSet = prepareStatement.executeQuery()
        val entityList = ArrayList<T>()
        while (resultSet.next()) {
            val clazz = classModel.clazz ?: throw IllegalArgumentException("classModel.clazz is null")
            val entity = resultSetToEntity(clazz, resultSet, classModel)
            entityList.add(entity as T)
        }
        return entityList
    }

    fun <T : IdEntity> selectList(connection: Connection, classModel: ClassModel, vararg ktPropertiesPairs: Pair<KProperty1<T, *>, Any?>): ArrayList<T> {
        val whereSql = initWhereSql(ktPropertiesPairs, classModel)
        val sql = """
select * from ${classModel.tableName} where ${whereSql}
        """.trimIndent()
        logInfo("selectList sql is $sql")
        val prepareStatement = connection.prepareStatement(sql)
        val resultSet = prepareStatement.executeQuery()
        val entityList = ArrayList<T>()
        while (resultSet.next()) {
            val clazz = classModel.clazz ?: throw IllegalArgumentException("classModel.clazz is null")
            val entity = resultSetToEntity(clazz, resultSet, classModel)
            entityList.add(entity as T)
        }
        return entityList
    }

    fun <T : IdEntity> selectOne(connection: Connection, classModel: ClassModel, vararg ktPropertiesPairs: Pair<KProperty1<T, *>, Any?>): T? {
        val whereSql = initWhereSql(ktPropertiesPairs, classModel)
        val sql = """
select * from ${classModel.tableName} where ${whereSql} limit 1
        """.trimIndent()
        logInfo("selectOne sql is $sql")
        val prepareStatement = connection.prepareStatement(sql)
        val resultSet = prepareStatement.executeQuery()
        while (resultSet.next()) {
            val clazz = classModel.clazz ?: throw IllegalArgumentException("classModel.clazz is null")
            val entity = resultSetToEntity(clazz, resultSet, classModel)
            return entity as T
        }
        return null
    }

    private fun <T : IdEntity> initWhereSql(ktPropertiesPairs: Array<out Pair<KProperty1<T, *>, Any?>>, classModel: ClassModel): String {
        val whereSql = ktPropertiesPairs.map {
            val fieldName = it.first.name
            val fieldValue = it.second
            val fieldModel = classModel.fieldList.filter {
                it.name == fieldName
            }.firstOrNull()
            if (fieldModel == null) {
                null
            } else {
                val valueToString = valueToString(fieldValue)
                "`${fieldModel.columnName}` = ${valueToString}"
            }
        }.filter {
            it != null
        }.joinToString(" and ")
        return whereSql
    }

    fun insertBatchSql(classModel: ClassModel, insertEntities: List<IdEntity>): String {
        for (insertEntity in insertEntities) {
            initInsertEntity(insertEntity)
        }

        val columns = classModel.fieldList.filter {
            it.columnName != "id"
        }.sortedBy {
            it.sort
        }.map {
            "`" + it.columnName + "`"
        }.joinToString(", ")

        val fields = ReflectUtil.getFields(classModel.clazz)
        val valuesSql = insertEntities.map { entity ->
            val values = classModel.fieldList.filter {
                it.columnName != "id"
            }.sortedBy {
                it.sort
            }.map {
                val fieldName = it.name
                val declaredField = fields.filter {
                    it.name == fieldName
                }.firstOrNull()
                if (declaredField == null) {
                    null
                } else {
                    declaredField.isAccessible = true
                    val fieldValue = declaredField.get(entity)
                    fieldValue
                }
            }.joinToString(", ") {
                valueToString(it)
            }
            "(${values})"
        }.joinToString(", ")
        val sql = """
insert into ${classModel.tableName} (${columns}) values ${valuesSql}
        """.trimIndent()
        return sql
    }

    fun insertSql(classModel: ClassModel, insertEntity: IdEntity): String {
        initInsertEntity(insertEntity)

        val columns = classModel.fieldList.filter {
            it.columnName != "id"
        }.sortedBy {
            it.sort
        }.map {
            "`" + it.columnName + "`"
        }.joinToString(", ")

        val fields = ReflectUtil.getFields(classModel.clazz)
        val values = classModel.fieldList.filter {
            it.columnName != "id"
        }.sortedBy {
            it.sort
        }.map {
            val fieldName = it.name
            val declaredField = fields.filter {
                it.name == fieldName
            }.firstOrNull()
            if (declaredField == null) {
                null
            } else {
                declaredField.isAccessible = true
                val fieldValue = declaredField.get(insertEntity)
                fieldValue
            }
        }.joinToString(", ") {
            valueToString(it)
        }
        val sql = """
insert into ${classModel.tableName} (${columns}) values (${values})
        """.trimIndent()
        return sql
    }

    private fun initInsertEntity(insertEntity: IdEntity) {
        insertEntity.createTime = if (insertEntity.createTime == null) {
            Date()
        } else {
            insertEntity.createTime
        }

        if (insertEntity is BaseEntity) {
            insertEntity.del = 0
            insertEntity.version = 1
        }

        if (insertEntity is BaseUserEntity) {
            insertEntity.createUser = if (insertEntity.createUser == null) {
                0L
            } else {
                insertEntity.createUser
            }
        }

        if (insertEntity is BaseUserEntity) {
            insertEntity.updateUser = if (insertEntity.updateUser == null) {
                0L
            } else {
                insertEntity.updateUser
            }
        }

        insertEntity.updateTime = if (insertEntity.updateTime == null) {
            Date()
        } else {
            insertEntity.updateTime
        }
    }

    fun updateSql(classModel: ClassModel, updateEntity: IdEntity): String {
        updateEntity.updateTime = if (updateEntity.updateTime == null) {
            Date()
        } else {
            updateEntity.updateTime
        }

        if (updateEntity is BaseUserEntity) {
            updateEntity.updateUser = if (updateEntity.updateUser == null) {
                0L
            } else {
                updateEntity.updateUser
            }
        }

        if (updateEntity is BaseEntity) {
            updateEntity.version == if (updateEntity.version == 0) {
                1
            } else {
                updateEntity.version + 1
            }
        }

        val fields = ReflectUtil.getFields(classModel.clazz)
        val pairs = classModel.fieldList.filter {
            it.columnName != "id"
        }.sortedBy {
            it.sort
        }.map {
            val fieldName = it.name
            val declaredField = fields.filter {
                it.name == fieldName
            }.firstOrNull()
            if (declaredField == null) {
                null
            } else {
                declaredField.isAccessible = true
                val fieldValue = declaredField.get(updateEntity)
                val pairValue = valueToString(fieldValue)
                val pairString = "`${it.columnName}` = ${pairValue}"
                pairString
            }

        }
            .filterNotNull()
            .joinToString(", ")
        val sql = """
update ${classModel.tableName} set ${pairs} where id = ${updateEntity.id}
        """.trimIndent()
        return sql
    }

    fun insertBatch(connection: Connection, classModel: ClassModel, insertEntityList: List<IdEntity>, onSql: ((sql: String) -> String)? = null): Int {
        if (insertEntityList.isEmpty()) {
            return 0
        }
        var sql = insertBatchSql(classModel, insertEntityList)

        sql = onSql?.invoke(sql) ?: sql
        logInfo("insert sql is $sql")
        val prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        val affectRows = prepareStatement.executeUpdate()
        return affectRows
    }

    fun valueToString(it: Any?): String {
        return when (it) {
            null -> "null"
            is String -> "'$it'"
            is Date -> "'${TimeUtil.dateToTimeString(it, TimeUtil.yyyyMMddHHmmss_HYPHEN)}'"
            is Boolean -> if (it) "1" else "0"
            else -> "'$it'"
        }
    }
}